﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlServerCe;
using System.Data;
using XLCS.Common;
using System.Windows.Forms;
using System.ServiceProcess;
using System.IO;
using System.Diagnostics;
using WPF_DbCenter.DbEnginer;
using EE.SqlCe;
using WPF_DbCenter.SqlCE.DbEnginer;

namespace WPF_DbCenter.CommonMethod
{
    /// <summary>
    /// utility method to Sync data between some databases
    /// </summary>
    class CommonUtil
    {
        /// <summary>
        /// 
        /// </summary>
        /// <param name="str"></param>
        /// <returns></returns>
        public static bool SwitchToBool(object str)
        {
            if (str == null)
            {
                return false;
            }

            return str.ToString() == "Yes" ? true : false;
        }

        /// <summary>
        /// Accesss to sqlce
        /// </summary>
        /// <param name="p"></param>
        /// <param name="sdfFile"></param>
        public static bool ConvertMdbToSdf(string mdbFile, string sdfFile,bool NeedCopyData,string targetDbPwd)
        {
            bool result = false;

            if (!File.Exists(mdbFile))
            {
                MessageBox.Show("Access file lost,this action will cancel");
                return false;
            }

            IDbEnginer accessEg = new AccessEnginer();
            if (!accessEg.OpenDatabase(mdbFile))
            {
                MessageBox.Show("Read access database failure,this action will cancel");
                return false;
            }
            //Filter system table 
            List<string> tableList = new List<string>();
            foreach (string item in accessEg.TableList)
            {
                if (!item.StartsWith("MSys"))
                {
                    tableList.Add(item);
                }
            }

            if (tableList == null)
            {
                MessageBox.Show("This access database has no table ,this action will cancel");
                return false;
            }
            if (!File.Exists(sdfFile))
            {
                if (!App.MainEngineer.CreateDataBase(sdfFile))
                {
                    MessageBox.Show("Create sql ce database failure,this action will cancel");
                    return false;
                }
            }

            IDbEnginer eg = new CEEnginer();
            if (string.IsNullOrEmpty(targetDbPwd))
            {
                eg.OpenDatabase(sdfFile);
            }
            else
            {
                eg.OpenDatabase(sdfFile, targetDbPwd);
            }

            try
            {
                foreach (string tableName in tableList)
                {
                    string sqlCeTableName = tableName;
                    //if (Properties.Settings.Default.IsAllowAutoParseInvalidCharsInTableName)
                    //{
                    //   sqlCeTableName= sqlCeTableName.Replace(" ", "");
                    //}

                    string strconnection = string.Format("provider=microsoft.jet.oledb.4.0;data source={0}", mdbFile);
                    ADODB.Connection conn = new ADODB.ConnectionClass();
                    //conn.ConnectionString = strconnection;
                    conn.Open(strconnection, "Admin", "", 0);
                    //Prepare to retrive schema info from access via COM
                    ADOX.Catalog catelog = new ADOX.CatalogClass();
                    catelog.let_ActiveConnection(conn);
                    ADOX.Table tempTable = catelog.Tables[tableName];
                    
                    //Start Generate the Create Sdf table command
                    string tempCreateTableCmd = string.Empty;
                    tempCreateTableCmd = String.Format("CREATE TABLE [{0}] ", sqlCeTableName);
                    string tempSechma = string.Empty;
                    
                    for (int i = 0; i < tempTable.Columns.Count; i++)
                    {
                        Debug.WriteLine("Source Field Name ------>"+tempTable.Columns[i].Name);
                        tempSechma += String.Format("{0} {1},",
                            tempTable.Columns[i].Name, 
                            ParseADODbTypeToSqlCeDbType(tempTable.Columns[i].Type.ToString(),
                            tempTable.Columns[i].DefinedSize)
                            );
                    }

                    tempSechma = tempSechma.Substring(0, tempSechma.Length - 1);
                    tempCreateTableCmd = String.Format("{0} ({1})", tempCreateTableCmd, tempSechma);

                    if (!eg.ExecuteSimpleCommand(tempCreateTableCmd))
                    {
                        return false;
                    }
                    if (NeedCopyData)
                    {
                        CopyTable(accessEg, (SqlCeConnection)eg.DbHandler.CreateConnection(), string.Format("Select * from [{0}]", tableName), sqlCeTableName);
                    }
                }
                result = true;
            }
            catch (Exception ee)
            {
                ProcessException.DisplayErrors(ee);
                ((SqlCeDatabase)eg.DbHandler).CloseSharedConnection();
                //if (File.Exists(sdfFile))
                //{
                //    File.Delete(sdfFile);
                //}
            }


            return result;
        }

        /// <summary>
        /// mysql to sqlce
        /// </summary>
        /// <param name="server"></param>
        /// <param name="database"></param>
        /// <param name="username"></param>
        /// <param name="pwd"></param>
        /// <param name="sdfFile"></param>
        /// <param name="isCopyData"></param>
        /// <returns></returns>
        public static bool SyncDataFromMysqlToSqlCE(string server,string database,string username,string pwd,string sdfFile,bool isCopyData)
        {
            bool result = false;

            if (string.IsNullOrEmpty(server) || string.IsNullOrEmpty(database) || string.IsNullOrEmpty(username) || string.IsNullOrEmpty(sdfFile))
            {
                throw new Exception("Not valid parameters");
            }

            if (!App.MainEngineer.CreateDataBase(sdfFile))
            {
                MessageBox.Show("Create sql ce database failure,this action will cancel");
                return false;
            }

            IDbEnginer eg = new CEEnginer();
            eg.OpenDatabase(sdfFile);

            IDbEnginer dbE = new MySqlEnginer();
            try
            {
                dbE.CustomHandler.Open(new CoreEA.LoginDbInfo(server, database, "", username, pwd));

                List<string> tableList = dbE.CustomHandler.GetTableListInDatabase(database);

                if (tableList.Count <= 0)
                {
                    MessageBox.Show("This database has no table could be sync");
                    return false;
                }

                foreach (string tableName in tableList)
                {
                    string sqlCeTableName = tableName;
                    //if (Properties.Settings.Default.IsAllowAutoParseInvalidCharsInTableName)
                    //{
                    //   sqlCeTableName= sqlCeTableName.Replace(" ", "");
                    //}

                    DataTable tempTable = ((DataSet)dbE.CustomHandler.ExecuteSqlCmdAndReturnAllResult(
                        string.Format("{0} where Table_Name='{1}'", MySqlEnginer.GetColumnSchemaInfoString, tableName))).Tables[0];

                    //Start Generate the Create Sdf table command
                    string tempCreateTableCmd = string.Empty;
                    tempCreateTableCmd = String.Format("CREATE TABLE [{0}] ", sqlCeTableName);
                    string tempSechma = string.Empty;

                    for (int i = 0; i < tempTable.Rows.Count; i++)
                    {
                        Debug.WriteLine("Source Field Name ------>" + tempTable.Rows[i]["COLUMN_NAME"].ToString());
                        tempSechma += String.Format("{0} {1},", tempTable.Rows[i]["COLUMN_NAME"].ToString(),
                            CommonUtil.ParseMySqlDbTypeToSqlCeDbType(tempTable.Rows[i]["COLUMN_TYPE"].ToString()));
                    }

                    tempSechma = tempSechma.Substring(0, tempSechma.Length - 1);
                    tempCreateTableCmd = String.Format("{0} ({1})", tempCreateTableCmd, tempSechma);

                    if (!eg.ExecuteSimpleCommand(tempCreateTableCmd))
                    {
                        throw new Exception(string.Format("Create table {0} error", tableName));
                    }

                    if (isCopyData)
                    {
                        CommonUtil.CopyTable(dbE, (SqlCeConnection)eg.DbHandler.CreateConnection(), String.Format("select * from `{0}`",tableName), sqlCeTableName);
                    }

                    result = true;
                }

            }
            catch (Exception ee)
            {
                ProcessException.DisplayErrors(ee);
                ((SqlCeDatabase)eg.DbHandler).CloseSharedConnection();
                if (File.Exists(sdfFile))
                {
                    File.Delete(sdfFile);
                }
            }

            return result;
        }

        /// <summary>
        /// Sqlserver to sqlce
        /// </summary>
        /// <param name="server"></param>
        /// <param name="database"></param>
        /// <param name="username"></param>
        /// <param name="pwd"></param>
        /// <param name="sdfFile"></param>
        /// <param name="isCopyData"></param>
        /// <param name="isTrustedConnection"></param>
        /// <param name="prcessTableList">Is this list is empty , then ignore this para</param>
        /// <returns></returns>
        public static bool SyncDataFromSqlServerToSqlCE(string server, string database, string username, string pwd,
            string sdfFile, bool isCopyData,bool isTrustedConnection,List<string> prcessTableList)
        {
            bool result = false;


            if (string.IsNullOrEmpty(server) || string.IsNullOrEmpty(database) || string.IsNullOrEmpty(username) || string.IsNullOrEmpty(sdfFile))
            {
                throw new Exception("Not valid parameters");
            }
            //IF the ce database not existed ,then create it . 
            if (!File.Exists(sdfFile))
            {
                if (!App.MainEngineer.CreateDataBase(sdfFile))
                {
                    MessageBox.Show("Create sql ce database failure,this action will cancel");
                    return false;
                }
            }

            IDbEnginer eg = new CEEnginer();
            eg.OpenDatabase(sdfFile);

            IDbEnginer dbE = new SqlServerEnginer();
            try
            {
                CoreEA.LoginDbInfo info=new CoreEA.LoginDbInfo(server, database, "", username, pwd);
                info.IsTrustedConn = isTrustedConnection;
                dbE.CustomHandler.Open(info );

                List<string> tableList = dbE.CustomHandler.GetTableListInDatabase(database);
                if (tableList.Count <= 0)
                {
                    MessageBox.Show("This database has no table could be sync");
                    return false;
                }
                foreach (string tableName in tableList)
                {
                    if (prcessTableList.Count > 0)
                    {
                        //If not in the need process table list ,then do not process it . 
                        if (!prcessTableList.Contains(tableName))
                        {
                            continue;
                        }
                    }

                    string sqlCeTableName = tableName;
                    //if (Properties.Settings.Default.IsAllowAutoParseInvalidCharsInTableName)
                    //{
                    //   sqlCeTableName= sqlCeTableName.Replace(" ", "");
                    //}

                    DataTable tempTable = ((DataSet)dbE.CustomHandler.ExecuteSqlCmdAndReturnAllResult(string.Format("{0} where Table_Name='{1}'",SqlServerEnginer.GetColumnSchemaInfoString, tableName))).Tables[0];

                    //Start Generate the Create Sdf table command
                    string tempCreateTableCmd = string.Empty;
                    tempCreateTableCmd = String.Format("CREATE TABLE [{0}] ", sqlCeTableName);
                    string tempSechma = string.Empty;

                    for (int i = 0; i < tempTable.Rows.Count; i++)
                    {
                        Debug.WriteLine("Source Field Name ------>" + tempTable.Rows[i]["COLUMN_NAME"].ToString());
                        int length =50;

                        if (tempTable.Rows[i]["CHARACTER_MAXIMUM_LENGTH"]!=DBNull.Value)
                        {
                            length = int.Parse(tempTable.Rows[i]["CHARACTER_MAXIMUM_LENGTH"].ToString());
                        }

                        string appendix=CommonUtil.ParseSqlServerDbTypeToSqlCeDbType(tempTable.Rows[i]["DATA_TYPE"].ToString(),length);

                        tempSechma += String.Format("{0} {1},", tempTable.Rows[i]["COLUMN_NAME"].ToString(), appendix);
                    }

                    tempSechma = tempSechma.Substring(0, tempSechma.Length - 1);
                    tempCreateTableCmd = String.Format("{0} ({1})", tempCreateTableCmd, tempSechma);

                    if (!eg.ExecuteSimpleCommand(tempCreateTableCmd))
                    {
                        throw new Exception(string.Format("Create table {0} error", tableName));
                    }

                    if (isCopyData)
                    {
                        CommonUtil.CopyTable(dbE, (SqlCeConnection)eg.DbHandler.CreateConnection(), String.Format("select * from [{0}]", tableName), sqlCeTableName);
                    }

                    result = true;
                }

            }
            catch (Exception ee)
            {
                ProcessException.DisplayErrors(ee);
                ((SqlCeDatabase)eg.DbHandler).CloseSharedConnection();
                if (File.Exists(sdfFile))
                {
                    File.Delete(sdfFile);
                }
            }

            return result;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sqlServerType"></param>
        /// <param name="length"></param>
        /// <returns></returns>
        private static string ParseSqlServerDbTypeToSqlCeDbType(string sqlServerType, int length)
        {
            string ceType = string.Empty;
            switch (sqlServerType)
            {
                case "bitint":
                    ceType = "bigint";
                    break;

                case "binary":
                    ceType = String.Format("binary({0})", length);
                    break;
                case "char":
                    ceType = String.Format("char({0})", length);
                    break;
                case "varchar":
                    ceType = String.Format("varchar({0})", length);
                    break;
                case "varchar(MAX)":
                    ceType = String.Format("nTEXT", length);
                    break;
                case "Computed Columns":
                    //sql ce do not support this type
                    ceType = String.Format("nVarChar({0})", length);
                    break;
                case "datetime":
                    ceType = "datetime";
                    break;
                case "decimal":
                    ceType = "decimal ";
                    break;


                case "double precision":
                    ceType = "double precision";
                    break;

                case "float":
                    ceType = "float";
                    break;
                case "image":
                    ceType = "image";
                    break;
                case "int":
                    ceType = "integer";
                    break;

                case "money":
                    ceType = "money";
                    break;
                case "nchar":
                    ceType = String.Format("nchar({0})", length);
                    break;
                case "nvarchar":
                    ceType = String.Format("nvarchar({0})", length);
                    break;
                case "nvarchar(MAX)":
                    ceType = String.Format("nTEXT", length);
                    break;
                case "ntext":
                    ceType = String.Format("nTEXT", length);
                    break;

                case "dec":
                    ceType = "numeric";
                    break;

                case "real":
                    ceType = "real";
                    break;

                case "smalldatetime":
                    ceType = "datetime";
                    break;
                case "smallint ":
                    ceType = "smallint";
                    break;
                case "smallmoney":
                    ceType = "money";
                    break;
                case "variant ":
                    ceType = "nText";
                    break;
                case "text":
                    ceType = "nText";
                    break;
                case "tinyint":
                    ceType = "tinyint";
                    break;
                case "uniqueidentifier":
                    ceType = "uniqueidentifier";
                    break;
                case "varbinary":
                    ceType = string.Format("varbinary({)})",length);
                    break;
                case "varbinary(MAX)":
                    ceType = "image";
                    break;
                case "XML":
                    ceType = "nText";
                    break;
                default:
                    ceType = String.Format("nVarChar({0})", length);
                    Debug.WriteLine("Use default type ========>" + sqlServerType);
                    break;
            }
            return ceType;
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="adoType"></param>
        /// <returns></returns>
        public static string ParseADODbTypeToSqlCeDbType(string adoType,int length)
        {
            string ceType = string.Empty;
            switch (adoType)
            {
                //adTinyInt
                case "adUnsignedTinyInt":
                    ceType = "tinyint";
                    break;
                case "adBigInt":
                    ceType = "bigint";
                    break;
                case "adInteger":
                    ceType = "integer";
                    break;
                case "adSmallInt":
                    ceType = "smallint";
                    break;
                case "adBoolean":
                    ceType = "bit";
                    break;
                case "adNumeric":
                    ceType = "decimal ";
                    break;
                case "adCurrency":
                    ceType = "money";
                    break;
                case "adSingle":
                    ceType = "float";
                    break;
                case "adDouble":
                    ceType = "real";
                    break;
                case "adDate":
                    ceType = "datetime";
                    break;
                case "adDBDate":
                    ceType = "datetime";
                    break;
                case "adDBTime":
                    ceType = "datetime";
                    break;
                case "adWChar":
                    ceType = String.Format("nchar({0})",length);
                    break;
                case "adVarWChar":
                    ceType = String.Format("nvarchar({0})",length);
                    break;
                case "adChar":
                    ceType = "nchar(length)";
                    break;
                case "adVarChar":
                    ceType = String.Format("nvarchar({0})", length);
                    break;
                case "adBinary":
                    ceType = String.Format("binary({0})", length);
                    break;
                case "adVarBinary":
                    ceType = String.Format("varbinary({0})", length);
                    break;
                case "adLongVarBinary":
                    ceType = "image";
                    break;
                case "adLongVarWChar":
                    ceType = "ntext";
                    break;
                case "adLongVarChar":
                    ceType = "ntext";
                    break;
                case "adGuid":
                    ceType = "uniqueidentifier";
                    break;                 
                default:
                    ceType = String.Format("nVarChar({0})",length);
                    Debug.WriteLine("Use default type ========>"+adoType);
                    break;
            }
            return ceType;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="mysqlDbType"></param>
        /// <returns></returns>
        public static string ParseMySqlDbTypeToSqlCeDbType(string mysqlDbType)
        {
            string ceType = string.Empty;
            switch (mysqlDbType)
            {
                default:
                    ceType = "nVarChar(255)";
                    break;
            }
            return ceType;
        }

        /// <summary>
        /// 
        /// </summary>
        public static readonly string MySqlServiceName = "MySQL";
        /// <summary>
        /// 
        /// </summary>
        public class ResultArgs
        {
            public ServiceControllerStatus status { get; set; }
            public bool HasThisService { get; set; }
        }

        public static bool StartServices(string serviceName)
        {
            bool result = false;
            ServiceController[] f = ServiceController.GetServices();

            foreach (ServiceController c in f)
            {
                if (c.ServiceName == MySqlServiceName)
                {
                    if (c.Status != ServiceControllerStatus.Running)
                    {
                        try
                        {
                            c.Start();
                            result = true;
                            break;
                        }
                        catch (Exception ee)
                        {
                            result = false;
                            MessageBox.Show(ee.Message);
                        }
                    }

                }
            }

            return result;
        }

        /// <summary>
        /// 
        /// </summary>
        public static ResultArgs DetectMySqlService()
        {
            ServiceControllerStatus Cstatus = ServiceControllerStatus.StopPending;
            bool findMySql = false;
            string lab_ServiceStatus = string.Empty;
            ServiceController[] f = ServiceController.GetServices();
            foreach (ServiceController c in f)
            {
                if (c.ServiceName == MySqlServiceName)
                {
                    Cstatus = c.Status;
                    findMySql = true;
                    break;
                }
            }

            return new ResultArgs() { HasThisService = findMySql, status = Cstatus };

        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="srcEngine"></param>
        /// <param name="destConn">Must Sqlce Connection Due to SqlCeResultSet Object</param>
        /// <param name="queryString"></param>
        /// <param name="destTableName"></param>
        public static void CopyTable(
            IDbEnginer srcEngine,
            SqlCeConnection destConn,
            string queryString,
            string destTableName)
        {
            IDbCommand srcCommand = srcEngine.CustomHandler.GetConnection().CreateCommand();
            srcCommand.CommandText = queryString;

            if (destConn.State == ConnectionState.Closed)
            {
                destConn.Open();
            }
            SqlCeCommand destCommand = destConn.CreateCommand();
            destCommand.CommandType = CommandType.TableDirect; //基于表的访问，性能更好
            destCommand.CommandText = destTableName;

            IDataReader srcReader = srcCommand.ExecuteReader();

            SqlCeResultSet resultSet = destCommand.ExecuteResultSet(
                ResultSetOptions.Sensitive |   //检测对数据源所做的更改
                ResultSetOptions.Scrollable |  //可以向前或向后滚动
                ResultSetOptions.Updatable); //允许更新数据

            object[] values;
            SqlCeUpdatableRecord record;
            //这个方法由于前面ADO读取的列信息已经被排序，所以和数据库中真实的RECORD排序冲突。
            //所以使用下面的新的方法，使用列名寻找
            //while (srcReader.Read())
            //{
            //    // 从源数据库表读取记录
            //    values = new object[srcReader.FieldCount];
            //    srcReader.GetValues(values);
                
            //    // 把记录写入到目标数据库表
            //    record = resultSet.CreateRecord() ;

            //    record.SetValues(values);
            //    resultSet.Insert(record);
            //}
            while (srcReader.Read())
            {
                values = new object[srcReader.FieldCount];
                srcReader.GetValues(values);
                record = resultSet.CreateRecord();
                for (int i = 0; i < srcReader.FieldCount; i++)
                {
                    record[srcReader.GetName(i)] = srcReader.GetValue(i);
                }
                resultSet.Insert(record);
            }
            
            srcReader.Close();
            resultSet.Close();

        }

        static List<string> _SqlCeDbTypeList = new List<string>();

        public static List<string> CeDataType
        {
            get
            {
                if (_SqlCeDbTypeList.Count == 0)
                {
                    _SqlCeDbTypeList.Add("tinyint");
                    _SqlCeDbTypeList.Add("bigint");
                    _SqlCeDbTypeList.Add("integer");
                    _SqlCeDbTypeList.Add("int");
                    _SqlCeDbTypeList.Add("smallint");
                    _SqlCeDbTypeList.Add("bit");
                    _SqlCeDbTypeList.Add("decimal");
                    _SqlCeDbTypeList.Add("money");
                    _SqlCeDbTypeList.Add("float");
                    _SqlCeDbTypeList.Add("real");
                    _SqlCeDbTypeList.Add("datetime");
                    _SqlCeDbTypeList.Add("nchar");
                    _SqlCeDbTypeList.Add("nvarchar");
                    _SqlCeDbTypeList.Add("binary");
                    _SqlCeDbTypeList.Add("varbinary");
                    _SqlCeDbTypeList.Add("image");
                    _SqlCeDbTypeList.Add("ntext");
                    _SqlCeDbTypeList.Add("uniqueidentifier");
                }
                return _SqlCeDbTypeList;
            }
        }

        internal static bool ConvertEXCELToSdf(string excelFile, string sdfFile, bool NeedCopyData, string targetDbPwd)
        {
            bool result = false;

            if (!File.Exists(excelFile))
            {
                MessageBox.Show("Excel file lost,this action will cancel");
                return false;
            }

            IDbEnginer excelEg = new ExcelEnginer();
            if (!excelEg.OpenDatabase(excelFile))
            {
                MessageBox.Show("Read access database failure,this action will cancel");
                return false;
            }

            List<string> tableList = excelEg.GetTableList();
            if (!File.Exists(sdfFile))
            {
                if (!App.MainEngineer.CreateDataBase(sdfFile))
                {
                    MessageBox.Show("Create sql ce database failure,this action will cancel");
                    return false;
                }
            }

            IDbEnginer eg = new CEEnginer();
            if (string.IsNullOrEmpty(targetDbPwd))
            {
                eg.OpenDatabase(sdfFile);
            }
            else
            {
                eg.OpenDatabase(sdfFile, targetDbPwd);
            }

            try
            {
                foreach (string tableName in tableList)
                {
                    string sqlCeTableName = tableName;
  
                    //string strconnection = 
                    //    string.Format("provider=microsoft.jet.oledb.4.0;data source={0};Extended Properties=\"Excel 8.0;HDR=YES;IMEX=0\"", excelFile);

                    string strconnection = CoreEA.ConnSTR.DbConnectionString.Excel.GetOleDbConnectionString(excelFile, true);

                    ADODB.Connection conn = new ADODB.ConnectionClass();
                    //conn.ConnectionString = strconnection;
                    conn.Open(strconnection, "", "", 0);

                    //Prepare to retrive schema info from access via COM
                    ADOX.Catalog catelog = new ADOX.CatalogClass();
                    catelog.let_ActiveConnection(conn);
                    ADOX.Table tempTable = catelog.Tables[tableName];

                    //Start Generate the Create Sdf table command
                    string tempCreateTableCmd = string.Empty;
                    tempCreateTableCmd = String.Format("CREATE TABLE [{0}] ", sqlCeTableName);
                    string tempSechma = string.Empty;

                    for (int i = 0; i < tempTable.Columns.Count; i++)
                    {
                        Debug.WriteLine("Source Field Name ------>" + tempTable.Columns[i].Name);
                        tempSechma += String.Format("{0} {1},",
                            tempTable.Columns[i].Name,
                            ParseADODbTypeToSqlCeDbType(tempTable.Columns[i].Type.ToString(),
                            tempTable.Columns[i].DefinedSize)
                            );
                    }


                    tempSechma = tempSechma.Substring(0, tempSechma.Length - 1);
                    tempCreateTableCmd = String.Format("{0} ({1})", tempCreateTableCmd, tempSechma);

                    if (!eg.ExecuteSimpleCommand(tempCreateTableCmd))
                    {
                        throw new Exception(string.Format("Create table {0} error", tableName));
                    }
                    if (NeedCopyData)
                    {
                        CopyTable(excelEg, (SqlCeConnection)eg.DbHandler.CreateConnection(),
                            string.Format("Select * from [{0}]", tableName), sqlCeTableName);
                    }
                }
                result = true;
            }
            catch (Exception ee)
            {
                ProcessException.DisplayErrors(ee);
                ((SqlCeDatabase)eg.DbHandler).CloseSharedConnection();
                //if (File.Exists(sdfFile))
                //{
                //    File.Delete(sdfFile);
                //}
            }


            return result;
        }

        /// <summary>
        /// Mose Like Excel 
        /// Just different in Connection String
        /// </summary>
        /// <param name="csvFile"></param>
        /// <param name="sdfFile"></param>
        /// <param name="p"></param>
        /// <param name="p_4"></param>
        /// <returns></returns>
        internal static bool ConvertCSVToSdf(string csvFile, string sdfFile, bool NeedCopyData, string targetDbPwd)
        {
            bool result = false;

            if (!File.Exists(csvFile))
            {
                MessageBox.Show("Excel file lost,this action will cancel");
                return false;
            }

            IDbEnginer csvEnginer = new CSVEngine();
            if (!csvEnginer.OpenDatabase(csvFile))
            {
                MessageBox.Show("Read access database failure,this action will cancel");
                return false;
            }

            List<string> tableList = csvEnginer.GetTableList();
            if (!File.Exists(sdfFile))
            {
                if (!App.MainEngineer.CreateDataBase(sdfFile))
                {
                    MessageBox.Show("Create sql ce database failure,this action will cancel");
                    return false;
                }
            }

            IDbEnginer eg = new CEEnginer();
            if (string.IsNullOrEmpty(targetDbPwd))
            {
                eg.OpenDatabase(sdfFile);
            }
            else
            {
                eg.OpenDatabase(sdfFile, targetDbPwd);
            }

            try
            {
                foreach (string tableName in tableList)
                {
                    string sqlCeTableName = tableName;

                    //string strconnection =
                    //    string.Format("provider=microsoft.jet.oledb.4.0;data source={0};Extended Properties=\"Excel 8.0;HDR=YES;IMEX=0\"", csvFile);

                    string strconnection = CoreEA.ConnSTR.DbConnectionString.TxtFile.OleDb_DelimitedColumns(csvFile, true);
                    ADODB.Connection conn = new ADODB.ConnectionClass();
                    //conn.ConnectionString = strconnection;
                    conn.Open(strconnection, "", "", 0);

                    //Prepare to retrive schema info from access via COM
                    ADOX.Catalog catelog = new ADOX.CatalogClass();
                    catelog.let_ActiveConnection(conn);
                    ADOX.Table tempTable = catelog.Tables[tableName];

                    //Start Generate the Create Sdf table command
                    string tempCreateTableCmd = string.Empty;
                    tempCreateTableCmd = String.Format("CREATE TABLE [{0}] ", sqlCeTableName);
                    string tempSechma = string.Empty;

                    for (int i = 0; i < tempTable.Columns.Count; i++)
                    {
                        Debug.WriteLine("Source Field Name ------>" + tempTable.Columns[i].Name);
                        tempSechma += String.Format("{0} {1},",
                            tempTable.Columns[i].Name,
                            ParseADODbTypeToSqlCeDbType(tempTable.Columns[i].Type.ToString(),
                            tempTable.Columns[i].DefinedSize)
                            );
                    }


                    tempSechma = tempSechma.Substring(0, tempSechma.Length - 1);
                    tempCreateTableCmd = String.Format("{0} ({1})", tempCreateTableCmd, tempSechma);

                    if (!eg.ExecuteSimpleCommand(tempCreateTableCmd))
                    {
                        throw new Exception(string.Format("Create table {0} error", tableName));
                    }
                    if (NeedCopyData)
                    {
                        CopyTable(csvEnginer, (SqlCeConnection)eg.DbHandler.CreateConnection(),
                            string.Format("Select * from [{0}]", tableName), sqlCeTableName);
                    }
                }
                result = true;
            }
            catch (Exception ee)
            {
                ProcessException.DisplayErrors(ee);
                ((SqlCeDatabase)eg.DbHandler).CloseSharedConnection();
                //if (File.Exists(sdfFile))
                //{
                //    File.Delete(sdfFile);
                //}
            }


            return result;
        }
    }
}
